package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import negocio.Tarjeta;
import negocio.Tecnico;
import controlador.Sistema;

public class perTecnico {
	private Sistema sist;
	private Tecnico tec=null;
	public Tecnico select(int DNI)
	{
		
		try {
			sist=Sistema.getInstance();
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Select * from Tecnico where DNI=?");
			ps.setInt(1, DNI);
			ResultSet rs=ps.executeQuery();
			if(rs.next())
			{
				tec=new Tecnico();
				tec.setNombre(rs.getString(1));
				tec.setApellido(rs.getString(2));
				tec.setDNI(rs.getInt(3));
				tec.setDomicilio(rs.getString(4));
				Tarjeta tar=new Tarjeta();
				tar.setNroTarjeta(rs.getInt(5));					
				tec.setTarjeta(tar);
				tec.setLegajo(rs.getInt(6));	
				tec.setSede(sist.buscarSede(rs.getInt(7)));
				tec.setEstado(rs.getBoolean(8));			
				//tec.setEstado(true);
				//System.out.println(rs.getInt(9)+", "+rs.getInt(7));
				tec.setTelefonoL(rs.getInt(9));
				//sist.agregarTecnico(tec);				
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return tec;
	}
	public Vector <Tecnico> select()
	{
		
		try {
			Vector <Tecnico> v=new Vector <Tecnico>();
			sist=Sistema.getInstance();
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Select * from Tecnico");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				tec=new Tecnico();
				tec.setNombre(rs.getString(1));
				tec.setApellido(rs.getString(2));
				tec.setDNI(rs.getInt(3));
				tec.setDomicilio(rs.getString(4));
				Tarjeta tar=new Tarjeta();
				tar.setNroTarjeta(rs.getInt(5));					
				tec.setTarjeta(tar);
				tec.setLegajo(rs.getInt(6));	
				tec.setSede(sist.buscarSede(rs.getInt(7)));
				tec.setEstado(rs.getBoolean(8));			
				//tec.setEstado(true);
				//System.out.println(rs.getInt(9)+", "+rs.getInt(7));
				tec.setTelefonoL(rs.getInt(9));
				//sist.agregarTecnico(tec);
				v.add(tec);
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			return v;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	public void delete(int DNI)
	{
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("update Tecnico set Estado = 0 where DNI=?");
			ps.setInt(1, DNI);
			ps.executeQuery();
			PoolConnection.getPoolConnection().releaseConnection(con);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insert(Tecnico t)
	{
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Insert into Tecnico values(?,?,?,?,?,?,?,?,?)");
			ps.setString(1, t.getNombre());
			ps.setString(2, t.getApellido());
			ps.setInt(3, t.getDNI());
			ps.setString(4, t.getDomicilio());//domicilio
			ps.setInt(5, t.getTarjeta().getNroTarjeta());
			ps.setInt(6, t.getLegajo());
			ps.setInt(7, t.getSede().getNroSede());
			ps.setBoolean(8, true);
			ps.setInt(9, t.getTelefonoL());
			ps.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void update(Tecnico t)
	{
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("update Tecnico set NOMBRE=?, APELLIDO=?, DOMICILIO=?, NROTARJETA=?," +
					" LEGAJO=?, IDSEDE=?, ESTADO=?, TELEFONO=? where DNI=?");
			ps.setString(1, t.getNombre());
			ps.setString(2, t.getApellido());			
			ps.setString(3, t.getDomicilio());//domicilio
			ps.setInt(4, t.getTarjeta().getNroTarjeta());
			ps.setInt(5, t.getLegajo());
			ps.setInt(6, t.getSede().getNroSede());
			ps.setBoolean(7, true);
			ps.setInt(8, t.getTelefonoL());
			ps.setInt(9, t.getDNI());
			ps.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
